<?php
include('config.php');
session_start();

$cmd = $_GET['cmd'];

switch ($cmd) {
	case "addUser":
		addUser($_GET['firstName'], $_GET['lastName'], $_GET['username'], $_GET['password'], $_GET['access_level'], $_GET['email']);
		break;
	case "faultyOccamPort":
		faultyOccamPort($_GET['ip'], $_GET['port'], $_GET['problemDescription']);
		break;
	case "changePassword":
		changePassword();
		break;
	case "updateProfile":
		break;
	case "faultySTB":
		faultySTB($_GET['serial'], $_GET['problemDescription']);
		break;
	case "faultyModem":
		faultyModem($_GET['serial'], $_GET['model'], $_GET['problemDescription']);
		break;
	case "ontIP":
		ontIP($_GET['blade_ip'], $_GET['port']);
		break;
	case "updateDevice":
		updateDevice($_GET['hostname'], $_GET['model'], $_GET['ip'], $_GET['serial']);
		break;
	case "addDevice":
		addDevice($_GET['hostname'], $_GET['model'], $_GET['ip'], $_GET['serial']);
		break;
	case "discoverDevices":
		discoverDevices($_GET['ip'], $_GET['end_ip'], $_GET['community']);
		break;
	case "deleteDevice":
		deleteDevice($_GET['device_id']);
		break;
	case "ip_lookup":
		ip_lookup($_GET['hostname']);
		break;
	case "admin_settings_update":
		admin_settings_update($_GET['table'], $_GET['column'], $_GET['value']);
		break;
	case "add_occam_copper_access_profile":
		add_occam_copper_access_profile($_GET['name'], $_GET['description']);
		break;
	case "update_occam_copper_access_profile":
		update_occam_copper_access_profile($_GET['profile_id'], $_GET['name'], $_GET['description']);
		break;
	case "delete_occam_copper_access_profile":
		delete_occam_copper_access_profile($_GET['profile_id']);
		break;
	case "add_occam_copper_dsl_profile":
		add_occam_copper_dsl_profile($_GET['name']);
		break;
	case "update_occam_copper_dsl_profile":
		update_occam_copper_dsl_profile($_GET['profile_id'], $_GET['name']);
		break;
	case "delete_occam_copper_dsl_profile":
		delete_occam_copper_dsl_profile($_GET['profile_id']);
		break;
	case "add_occam_fiber_ont_start_ip":
		add_occam_fiber_ont_start_ip($_GET['blade_ip'], $_GET['ont_start_ip']);
		break;
	case "update_occam_fiber_ont_start_ip":
		update_occam_fiber_ont_start_ip($_GET['blade_ip'], $_GET['ont_start_ip']);
		break;
	case "delete_table_row":
		delete_table_row($_GET['table'], $_GET['primary_key_name'], $_GET['primary_key_value']);
		break;
}//END of switch case





// Start of function definitions for DB queries

function addUser($firstName, $lastName, $username, $password, $access_level, $email) {
	$md5password = md5($password);
	$results = mysql_query("SELECT * FROM users WHERE username = '$username'");
	
	if (mysql_num_rows($results) == 0) {
		//If the username is NOT already in the DB, the insert the new user
		mysql_query("INSERT INTO users (firstName, lastName, username, password, access_level, email) VALUES ('$firstName', '$lastName', '$username', '$md5password', '$access_level', '$email')");
		echo "User added successfully<br>\n";
		$mail_message = "Welcome $firstName to the Occam Provisioning System!\nUsername: $username\nPassword: $password\n\nThis is going to be the body of the e-mail.\n\nSincerely,\nAdministrator";
		if (mail($email, 'User Registration', $mail_message)){
			echo "Registration e-mail sent\n";
		}
	}
	else {
		//If the username is already used in the DB, then return error
		echo "Username already in use";
	}
}

function changePassword() {
	$current_user = $_SESSION['myusername'];
	$newPassword = md5($_GET['password']);

	mysql_query("UPDATE users SET password = '$newPassword' WHERE username = '$current_user'");
	echo "Password change successful";
}

function faultyOccamPort($ip, $port, $problemDescription) {
	$current_user = $_SESSION['myusername'];

	mysql_query("INSERT INTO faulty_occam_ports (blade_ip, port, problem_description, submitted_by) VALUES ('$ip', '$port', '$problemDescription', '$current_user')");
	echo "Troubled port added successfully!";
}

function faultySTB($serial, $problemDescription) {
	$current_user = $_SESSION['myusername'];
	$serial = strtoupper($serial);
	$model = substr($serial, 0, 3);
	
	/*if (strlen($serial) == 15) {

		$manf_year = substr($serial, 6, 1);
                $manf_week = substr($serial, 7, 2);
                //  Figure the weeks worth of MS to add to total for manf date
                $manf_week_ms = $manf_week * 604800000;
                
                //  Convert the manufactured date to the format mm-dd-yy because
                //  that is what the strtotime() function wants to see.
                //  Use the first of the month since date is not given.
                $manf_date = "01-01-200" . $manf_year;

                //  Convert the manufactured date to a time() format showing seconds
                //  since 1/1/1970.  Then add 2 years (warranty length) on to that time.
                $manf_date = strtotime( '+1 year', strtotime($manf_date));
		$manf_total_ms = $manf_date + $manf_week_ms;
	*/
                //  Curent timestamp
                $current_time = time();
                
                //  If model is not blank, then continue request
                //if (!empty($model)) {
                    	if ($model == "BZX") {
                        	$model = "ADB2500";
                        }
                        else if ($model == "BQJ") {
                                $model = "ADB3800";
                        }
                        else if ($model == "BSV") {
                                $model = "ADB5810";
                        }
                        else {
                                $model = NULL;
                                echo "Not a valid serial number";
                        }
                        
                        //  If manufacture timestamp (+2 years) is greater then current timestamp
                        //  then submit the request.  Otherwise tell them it is out of warranty.
                        //if ($manf_total_ms > $current_time) {
                        
                                echo "Device is in warranty<br>";
                                echo "Model: " . $model . "<br>";
                                echo "Manufacture Date: " . $manf_date . "<br>";
                                echo "Current Date: " . $current_time . "<br>";
                                mysql_query("INSERT INTO faulty_stb (serial, model, problem_description, submitted_by) values ('$serial', '$model', '$problemDescription', '$current_user')");

                                echo "Faulty Modem with serial: $serial submitted successfully!";
                        //}
                        /*else {
                                echo "<span style=\"color: red; font-weight: bold\">Device is out of warranty</span><br>";
                                echo "Model: " . $model . "<br>";
                                echo "Manufacture Date: " . $manf_date . "<br>";
                                echo "Current Date: " . date('m-d-y', time()) . "<br>";
                        }
                }
                else {
                        echo "Model could not be derived from serial number";
                }
	}
	else {
		echo "Serial number is not long enough";
	}*/
}

function faultyModem($serial, $model, $problemDescription) {
	//if (strlen($serial) == 11) {
		$current_user = $_SESSION['myusername'];
		$serial = strtoupper($serial);
				mysql_query("INSERT INTO faulty_modem (serial, model, problem_description, submitted_by) values ('$serial', '$model', '$problemDescription', '$current_user')");
	
				echo "Faulty Modem with serial: $serial submitted successfully!";
}

function ontIP($blade_ip, $port) {
	//  Query the database for the starting IP that is assigned to port 1.
	$ip_result = mysql_query("SELECT * FROM ont_ip WHERE blade_ip = '$blade_ip'");

	//  Did the query find an entry in the database?
	if (mysql_num_rows($ip_result) != 0){
	        $ip_count_start = mysql_fetch_array($ip_result);
	        $ip_count_start = $ip_count_start['ip_start'];
		
		//  Find the value in the last octet of the IP address
	        $octet = explode(".", $ip_count_start);

		//  Add the port number to the starting ip and subtract 1
		//  This is done because the starting IP is assigned to port 1
		if ($port > 4) {
			$port = $port - 2; //this is done because 5 & 6 are skipped
		} 

		$i = $octet[3] + $port - 1;

		//  Rebuild the IP address in 4 octets seperated by "."
	        $ont_ip = $octet[0] . "." . $octet[1] . "." . $octet[2] . ".$i";
	}else{
	//  If there was no entry found in the database, return an error
		$ont_ip = "No IP was found...";
	}

	// Return the IP address or error
        echo $ont_ip;
        	
}

function updateDevice($hostname, $model, $ip, $serial) {
	//Attempt to add the changes to the database override everything, rather then find the changed value.
	$query = mysql_query("UPDATE devices SET device_name='$hostname', device_type='$model', device_serial='$serial' WHERE device_ip='$ip'");
	
	//Update will return TRUE on success
	if ($query == "TRUE") {
		echo "Device update Success\n";
		echo "Hostname: $hostname\n";
		echo "Model: $model\n";
		echo "IP: $ip\n";
		echo "Serial: $serial\n";
	} else {
		die('Invalid query: ' . mysql_error());
	}
}

function addDevice($hostname, $model, $ip, $serial) {
	//Attempt to add the device to the databse
	$query = mysql_query("INSERT INTO devices (device_name, device_type, device_ip, device_serial) VALUES ('$hostname', '$model', '$ip', '$serial')");

	if ($query == "TRUE") {
		echo "Device added Successfully";
	} else {
		die('Invalid query: ' . mysql_error());
	}

	//  Check to see if the model type is in the device_type table, insert it if it isn't
	$device_types_query = mysql_query("SELECT * FROM device_types WHERE device_model = '$model'");
	echo $model;
	if (mysql_num_rows($device_types_query) == 0) {
		if ($model == "6252" OR $model == "6252 02" OR $model == "6252 03") {
			$dsl_port_num = "48";	
			$eth_port_num = "6";	
		}
		else if ($model == "6312") {
			$dsl_port_num = NULL;
			$eth_port_num = "22";
		}
		else if ($model == "6316") {
			$dsl_port_num = NULL;
			$eth_port_num = "46";
		}
		//  If device_type is not in the table, insert it
		echo "device added $model";
		mysql_query("INSERT INTO device_types (device_model, device_dsl_ports, device_eth_ports) VALUES ('$model', '$dsl_port_num', '$eth_port_num')");
 
	} else {
		echo "NO device added";
		//  If device_type is in the table do nothing
	}
}

function discoverDevices($start_ip, $end_octet, $community) {
	echo "<table width='100%' cellspacing='0'>";
	echo "<tr><th>Model</th><th>Serial</th><th>Hostname</th><th>IP Address</th><th>Uptime</th><th></tr>";

	list($octet1, $octet2, $octet3, $octet4) = explode(".", $start_ip);
	//START for loop
	//@ symbol put in place to supress error being output to screen
	$inaccess_total = 0;
	$access_total = 0;

	// If the ending octet is greater then 254, set it to 254
	if ($end_octet > 254) {
	        $end_octet = "254";
	} //  If the ending octet is less then 1, set it to 1
	else if ($end_octet < 1) {
	        $end_octet = "1";
	}
	for ($i = $octet4; $i < $end_octet; $i++) {
	        $ip = "$octet1.$octet2.$octet3.$i";
	        if (@snmpget($ip, $community, "1.3.6.1.2.1.1.5.0", 300) == NULL) {
	                //echo "<tr><td colspan='2'>innaccessible</td>";
	                //echo "<td>$ip</td><td></td><td></td></tr>";
	                $inaccess_total++;
	        }
	        else {
	                $hostname = snmpget($ip, $community, "1.3.6.1.2.1.1.5.0");
	                $hostname = str_replace("STRING: ", "", $hostname);

	                $model = snmpget($ip, $community, "1.3.6.1.2.1.1.1.0");
	                $model = str_replace("STRING: BLC ", "", $model);

	                //$serial = snmpget($ip, $community, "1.3.6.1.4.1.6066.2.1.3.1.1.6");
	                $serial = @snmpget($ip, $community, "1.3.6.1.4.1.6066.2.1.3.1.1.6.0");
	                $serial = str_replace("STRING: ", "", $serial);
       		        $serial = eregi_replace("[\"\"]", "", $serial);

                	$uptime = snmpget($ip, $community, "1.3.6.1.2.1.1.3.0");
                	$uptime = str_replace("Timeticks: ", "", $uptime);
                	$uptime = eregi_replace("[()]", "", $uptime);
                	list($uptime) = explode(" ", $uptime);

                	//print($uptime); //Used for debugging
                	$days = floor($uptime / 8640000);
                	$hours = floor(($uptime - ($days * 8640000)) / 360000);
                	$minutes = floor(($uptime - ($days * 8640000) - ($hours * 360000)) / 6000);
                	if (strlen($minutes) < 2) {
                	        $minutes = "0" . $minutes;
                	}
                	if (strlen($hours) < 2) {
                	        $hours = "0" . $hours;
                	}
                	if ($days > 0) {
                        	$uptime = $days . " days " . $hours . ":" . $minutes;
                	} else {
                	        $uptime = "0 days " . $hours . ":" . $minutes;
                	}


                	//Query the databse to see if the device is already in the DB
                	$results = mysql_query("SELECT * FROM devices WHERE device_ip = '$ip'");
                	if (mysql_num_rows($results) < 1 ) {
                	        echo "<tr style='background: #57FFFF'><td>$model</td><td>$serial</td><td>$hostname</td><td>$ip</td><td>$uptime</td><td><input id='$hostname' type='button' value='Add' onClick='addDevice(\"$hostname\",\"$model\",\"$ip\",\"$serial\")'/></td></tr>\n";
                	        //Add device to the databse AJAX
                	} else {
                	        $row = mysql_fetch_array($results);
                	        if ($row['device_name'] != $hostname || $row['device_type'] != $model || $row['device_serial'] != $serial) {
                	                echo "<tr style='background: #FFFF57'><td>$model</td><td>$serial</td><td>$hostname</td><td>$ip</td><td>$uptime</td><td><input id='$hostname' type='button' value='Update' onClick='updateDevice(\"$hostname\",\"$model\",\"$ip\",\"$serial\")' /></td></tr>\n";
				        //Update device in database AJAX
                	        } else {
                	                //Device is in the database, and has current information.  No changes needed    
                	                //echo "<tr style='background: #efefef; color: #aeaeae'><td>$model</td><td>$hostname</td><td>$ip</td><td>$uptime</td><td><input type='button' value='Update' disabled='true'/></td></tr>";
                	        }
                	}
                	$access_total++;
        	}
	}//END for loop

	echo "</table>";
	
	echo "Inaccessible Total: $inaccess_total<br/>";
	echo "Accessible Total: $access_total<br/>";

}

function deleteDevice($device_id) {
	mysql_query("DELETE FROM devices WHERE device_id = '$device_id'");
}

function ip_lookup($hostname) {
	$results = mysql_query("SELECT * FROM devices WHERE device_name = '$hostname'");
	$row = mysql_fetch_array($results);
	$ip = $row['device_ip'];

	echo $ip;
}

function admin_settings_update($table, $column, $value) {
	//  Do an update to the database based on the table, column, and value passed
	mysql_query("UPDATE $table SET $column = '$value'");
	
	//  If the column is for the meta, we need to change the variable in the text file
	if ($column == "meta_ip_address") {
		$meta_ip_address = $value;
		$fp = fopen("../metaswitch/sdk/xml/soap/ShServiceTyped.wsdl", "r") or DIE ("Unable to read ShServiceTyped.wsdl");

		$buf = "";
		while($line = fgets($fp)){
		        if (preg_match("/wsdlsoap:address/", $line)) {
		                $line = "\t\t\t<wsdlsoap:address location=\"http://$meta_ip_address:8080/services/ShService\"/>\n";
		        }
		        $buf.= $line;
		}
		//Close the pointer to the readonly file
		fclose($fp);

		//  Write everything to the file
		$fp = fopen("../metaswitch/sdk/xml/soap/ShServiceTyped.wsdl", "w") or DIE ("Unable to read ShServiceTyped.wsdl");
		fwrite($fp, $buf);
		fclose($fp);
		echo "Successfully changed meta_ip_address in file";
	}
	echo "Success";
}

function add_occam_copper_access_profile($name, $description) {
	$query = mysql_query("INSERT INTO access_profiles (profile_name, profile_description) VALUES ('$name', '$description')");

	if ($query == "TRUE") {
		echo "Successful addition of $name";
	}
	else {
		echo "Failed addition of $name";
	}
}

function update_occam_copper_access_profile($profile_id, $name, $description) {
	$query = mysql_query("UPDATE access_profiles SET profile_name='$name', profile_description='$description' WHERE profile_id='$profile_id'");

	if ($query == "TRUE") {
		echo "Success";
	}
	else {
		echo "Failed";
	}
}

function delete_occam_copper_access_profile($profile_id) {
	$query = mysql_query("DELETE FROM access_profiles WHERE profile_id='$profile_id'");

	if ($query == "TRUE") {
		echo "Success";
	}
	else {
		echo "Failed";
	}
}

function add_occam_copper_dsl_profile($name) {
	$query = mysql_query("INSERT INTO dsl_profiles (dsl_profile_name) VALUES ('$name')");

	if ($query == "TRUE") {
		echo "Successful addition of $name";
	}
	else {
		echo "Failed addition of $name";
	}
}

function update_occam_copper_dsl_profile($profile_id, $name) {
	$query = mysql_query("UPDATE dsl_profiles SET dsl_profile_name='$name' WHERE dsl_profile_id='$profile_id'");

	if ($query == "TRUE") {
		echo "Success";
	}
	else {
		echo "Failed";
	}
}

function delete_occam_copper_dsl_profile($profile_id) {
	$query = mysql_query("DELETE FROM dsl_profiles WHERE dsl_profile_id='$profile_id'");

	if ($query == "TRUE") {
		echo "Success";
	}
	else {
		echo "Failed";
	}
}

function add_occam_fiber_ont_start_ip($blade_ip, $ont_start_ip) {
	$query = mysql_query("INSERT INTO ont_ip (blade_ip, ip_start) VALUES ('$blade_ip', '$ont_start_ip')");

	if ($query == "TRUE") {
		echo "Success";
	} else {
		echo "Failed";
	}
}

function update_occam_fiber_ont_start_ip($blade_ip, $ont_start_ip) {
	$query = mysql_query("UPDATE ont_ip SET ip_start = '$ont_start_ip' WHERE blade_ip = '$blade_ip'");

	if ($query == "TRUE") {
		echo "Success";
	} else {
		echo "Failed";
	}
}

function delete_table_row($table, $primary_key_name, $primary_key_value) {
	$query = mysql_query("DELETE FROM $table WHERE $primary_key_name ='$primary_key_value'");

	if ($query == "TRUE") {
		echo "Success";
	}
	else {
		echo "Failed";
	}
}

?>
